﻿using MySql.Data.MySqlClient;
using NetMySql.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace NetMySql.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }
        public static int nameIndex = 1;
        public static string connectionString = ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString;
        [HttpPost]
        public ActionResult UserAdd(string type)
        {
            User users = new User();
            users.Id = Guid.NewGuid().ToString();
            users.Name = "Bruce"+(nameIndex++);
            users.Age = 27;
            users.Sex = "Man";
            string sql = "insert into user (Id,Name,Age,Sex) values ('"+users.Id+"','"+users.Name+"',"+users.Age+",'"+users.Sex+"') ";
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand(sql, connection);
            connection.Open();
            int success = cmd.ExecuteNonQuery();
            connection.Close();
            return Json(new { success = success > 0, msg = success > 0 ? "成功":"失败" });
        }
        [HttpPost]
        public ActionResult UserEdit(string type)
        {
            string sql = "update `user` set `Name`='EditTest' where `Name` = 'Bruce3' ";
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand(sql, connection);
            connection.Open();
            int success = cmd.ExecuteNonQuery();
            connection.Close();
            return Json(new { success = success > 0, msg = success > 0 ? "成功" : "失败" });
        }

        [HttpPost]
        public ActionResult UserDel(string type)
        {
            string sql = "delete from user where Age=27 ";
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand(sql, connection);
            connection.Open();
            int success = cmd.ExecuteNonQuery();
            connection.Close();
            return Json(new { success = success > 0, msg = success > 0 ? "成功" : "失败" });
        }
        [HttpPost]
        public ActionResult UserQuery(string type)
        {
            DataTable dt = MySqlHelper.ExecuteDataTable("select * from user");
            return Json(new { success = dt.Rows.Count > 0, msg = dt.Rows.Count > 0 ? "成功" : "失败",data = dt.Rows.Count });
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}